const ADODB = require('node-adodb')
if (process.env.NODE_ENV !== 'development') {
    ADODB.PATH = './resources/adodb.js'
}
const connection = ADODB.open(
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=rfdata.mdb;jet oledb:database password=rtmartedp;Persist Security Info=False'
)

exports.findCategories = async () => {
    return await connection.query('SELECT DISTINCT FNO FROM rfitem')
}

exports.findAll = async () => {
    return await connection.query(`SELECT rfitem.RFcode, rfitem.sskb, rfitem.FNO, (SELECT top 1 rfzt FROM rflsz WHERE rfcode = rfitem.RFcode ORDER BY id DESC) as rfzt
        FROM rfitem ORDER BY rfitem.RFcode ASC`)
}

exports.insert = async (rfcode, name, status, rfdate, rftime, rfbmp) => {
    await connection.execute(
        `insert into rflsz (rfcode, rfsskb, rfzt, rfdate, rftime, rfbmp) values ('${rfcode}','${name}','${status}','${rfdate}','${rftime}','${rfbmp}')`
    )
}

exports.find10 = async rfcode => {
    return await connection.query(
        `SELECT TOP 10 id, rfcode, rfsskb, rfzt, format(rfdate,'yyyy-mm-dd') as rfdate, format(rftime,'HH:mm:ss') as rftime, rfbmp from rflsz where rfcode='${rfcode}' order by id desc`
    )
}

exports.findLast10 = async () => {
    return await connection.query('SELECT TOP 10 id, rfcode, rfsskb, rfzt from rflsz order by id desc')
}

exports.delOne = async rfcode => {
    await connection.execute(`DELETE FROM rfitem WHERE RFcode='${rfcode}'`)
    await connection.execute(`DELETE FROM rflsz WHERE rfcode='${rfcode}'`)
}

exports.insertOne = async (rfcode, sskb, FNO) => {
    await connection.execute(`INSERT INTO rfitem (RFcode, sskb, FNO) VALUES ('${rfcode}', '${sskb}', '${FNO}')`)
}

exports.updateOne = async (oldRfcode, rfcode, sskb, FNO) => {
    await connection.execute(`UPDATE rfitem SET RFcode='${rfcode}', sskb='${sskb}', FNO='${FNO}' WHERE RFcode='${oldRfcode}'`)
    if (oldRfcode !== rfcode) {
        await connection.execute(`UPDATE rflsz SET rfcode='${rfcode}' WHERE rfcode='${oldRfcode}'`)
    }
}

exports.checkRfcode = async rfcode => {
    return await connection.query(`SELECT TOP 1 RFcode FROM rfitem WHERE RFcode='${rfcode}'`)
}
